package yu.zhou.njupt.importdataui.core.impl;

import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Properties;

import org.apache.commons.dbutils.DbUtils;

import yu.zhou.njupt.importdataui.core.Importer;
import yu.zhou.njupt.importdataui.entity.ImportResult;
import yu.zhou.njupt.importdataui.util.RegexUtil;

import com.google.common.base.Joiner;


public class AccessImporter extends Importer{
    
    Connection sourceConn = null;
    
    public AccessImporter(Connection conn) throws Exception {
        super(conn);
    }

    public ImportResult importData(File sourceFile) {
        ImportResult importResult = null;
        String filename = sourceFile.getName();
        String partKey = RegexUtil.getDateStr(filename);
        Statement s = null;              // 用于在目标库中建表
        PreparedStatement ps = null;                       // 用于向目标库中的表插入数据
        ResultSet rs = null;
        String createTableStr = null;                      //表语句
        String insertDataStr = null;                       // 插入数据语句
        int commitCount = 0;                               // 一次提交的记录数

        try {
            /* 连接源数据库(mdb文件) */
            Properties props = new Properties();
            props.put("charSet", "gb2312");                //这里是为了解决中文乱码   
            props.put("user", "");   
            props.put("password", "");   
            String dbur1 = "jdbc:odbc:driver=Microsoft Access Driver (*.mdb, *.accdb);DBQ=" + 
                    sourceFile.getAbsolutePath().replace("\\", "\\\\");  
            Connection sourceConn = DriverManager.getConnection(dbur1, props);

            /* 获得源数据库中的所有表名 */
            DatabaseMetaData sourceDbMetaData = sourceConn.getMetaData();
            ResultSet sourceTables = sourceDbMetaData.getTables(null, null, null, new String[]{"TABLE"});
            Statement sourceSt = sourceConn.createStatement(); // 用于从源数据库中获取数据
            ResultSet sourceRs = null;
            ResultSetMetaData sourceRsMetaData = null;

            while (sourceTables.next()) {
                s = conn.createStatement();
                commitCount = 0;
                String tableName = sourceTables.getString(3);
                sourceRs = sourceSt.executeQuery("select * from \"" + tableName + "\"");
                sourceRsMetaData = sourceRs.getMetaData();           // 用于获取源数据表中的列
                int columnCount = sourceRsMetaData.getColumnCount(); // 表的列数
                System.out.println(tableName);

                /* 构造出在目标数据库建表及插入数据语句 */
                String[] placeholders = new String[columnCount + (partKey == null ? 0 : 1)];     // 占位符数组
                String[] colNames = new String[columnCount + (partKey == null ? 0 : 1)];     // 列名数组
                String[] colTypeNames = new String[columnCount + (partKey == null ? 0 : 1)];     // 列的类型名数组
                
                for (int l = 0; l < columnCount; l++) {
                    colNames[l] = "\"" + sourceRsMetaData.getColumnName(l + 1).toUpperCase() + "\"";
                    colTypeNames[l] = sourceRsMetaData.getColumnTypeName(l + 1).toUpperCase();
                    placeholders[l] = new String("?");
                }
                
                if (partKey != null) {
                    colNames[columnCount] = "TABLE_VERSION";
                    colTypeNames[columnCount] = "PARTKEY";
                    placeholders[columnCount] = "'" + partKey + "'";
                }
                
                String[] cols = new String[colNames.length]; 
                for (int i = 0; i < cols.length; i++) {
                    cols[i] = colNames[i] + " " + convenrtType(colTypeNames[i]);
                }
                
                insertDataStr = new String("insert into \"" + tableName.toUpperCase() + "\"" +
                        " (" + Joiner.on(',').join(colNames) + ")" +
                        " values( " + Joiner.on(',').join(placeholders)) + ")";
                
                createTableStr = "create table \"" + tableName.toUpperCase() + "\"(" +
                        Joiner.on(',').join(cols) + ")";
                
                if (partKey != null) {
                    createTableStr += " partition by list(table_version)(partition p" + partKey +
                            " values('" + partKey + "'))";
                }

                /* 如果目标库不存在对应的表，在目标库建表 */
                ps = conn.prepareStatement("select count(1) from user_tables where table_name = ?");
                ps.setString(1, tableName.toUpperCase());
                rs = ps.executeQuery();
                if (rs.next()) {
                    if (rs.getInt(1) == 0) {
                        s.execute(createTableStr);
                    } else {
                    }
                }
                DbUtils.closeQuietly(ps);

                /* 如果目标表不存在对应分区，添加分区 */
                if (partKey != null) {
                    ps = conn.prepareStatement("select count(1) from user_tab_partitions where table_name = ? and partition_name = ?");
                    ps.setString(1, tableName.toUpperCase());
                    ps.setString(2, "P" + partKey);
                    rs = ps.executeQuery();
                    if (rs.next()) {
                        if (rs.getInt(1) == 0) {
                            s.execute("alter table \"" + tableName.toUpperCase()
                                    + "\" add partition p" + partKey + " values('"
                                    + partKey + "')");
                            s.close();
                        }
                    }
                    DbUtils.closeQuietly(ps);
                }

                /* 向目标库表中插入数据 */
                ps = conn.prepareStatement(insertDataStr);
                while (sourceRs.next()) {
                    for (int j = 1; j <= columnCount; j++) {
                        String columnTypeName = colTypeNames[j - 1]; // 列的类型
                        Object value = null;                         // 列的值

                        /* 根据列的类型获取列的值 */
                        if (columnTypeName.equals("VARCHAR") || columnTypeName.equals("LONGCHAR")) {
                            value = sourceRs.getString(j);
                        } else if (columnTypeName.equals("DATETIME")) {
                            value = sourceRs.getDate(j);
                        } else {
                            value = sourceRs.getInt(j);
                        }
                        ps.setObject(j, value);
                    }
                    ps.addBatch();
                    commitCount++;
                    if (commitCount % 1000 == 0) {
                        ps.executeBatch();
                        ps.clearBatch();
                        if (commitCount % 10000 == 0) {
                            System.out.println("  已处理" + commitCount + "记录");
                        }
                    }
                }
                ps.executeBatch();
            }
            importResult = new ImportResult(true, 0, 0);
        } catch (Exception e) {
            e.printStackTrace();
            importResult = new ImportResult(false, 0, 0);
        } finally {
            /* 关闭与数据库的连接 */
            DbUtils.closeQuietly(sourceConn);
            DbUtils.closeQuietly(ps);
            DbUtils.closeQuietly(s);
        }
        
        return importResult;
    }
    
    /** 将access数据类型转换为oracle数据类型(拼建表语句的时候使用) */
    public static String convenrtType(String columnTypeName) throws Exception {
        if (columnTypeName.toUpperCase().equals("VARCHAR")) {
            return "VARCHAR2(4000 CHAR)";
        } else if (columnTypeName.toUpperCase().equals("LONGCHAR")){
            return "VARCHAR2(4000 CHAR)";
        } else if (columnTypeName.toUpperCase().equals("DATETIME")){
            return "DATE";
        } else if (columnTypeName.toUpperCase().equals("COUNTER")){
            return "NUMBER";
        } else if (columnTypeName.toUpperCase().equals("INTEGER")){
            return "NUMBER";
        } else if (columnTypeName.toUpperCase().equals("DECIMAL")){
            return "NUMBER";
        } else if (columnTypeName.toUpperCase().equals("DOUBLE")){
            return "NUMBER";
        } else if (columnTypeName.toUpperCase().equals("BIT")){
            return "NUMBER(1,0)";
        } else if (columnTypeName.toUpperCase().equals("LONGBINARY")){
            return "NUMBER";
        } else if (columnTypeName.toUpperCase().equals("SMALLINT")){
            return "NUMBER";
        } else if (columnTypeName.toUpperCase().equals("PARTKEY")){
            return "VARCHAR2(20 CHAR)";
        } else {
            throw new Exception("数据类型错误:" + columnTypeName);
        }
    }

}
